/**
 * Project Name:BluemanTeam_shopping
 * File Name:BizDaoImpl.java
 * Package Name:com.shopping.dao.impl
 * Date:2018年5月6日下午1:16:00
 * Copyright (c) 2018, bluemobi All Rights Reserved.
 */

package com.shopping.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shopping.dao.BaseDao;
import com.shopping.dao.BizDao;
import com.shopping.entity.BigTypeForm;
import com.shopping.entity.GoodsDetail;
import com.shopping.entity.GoodsForm;
import com.shopping.entity.Manager;
import com.shopping.entity.Member;
import com.shopping.entity.SmallTypeForm;
import com.shopping.entity.TbAffiche;
import com.shopping.entity.TbBigType;
import com.shopping.entity.TbGoods;
import com.shopping.entity.TbLink;
import com.shopping.entity.TbManager;
import com.shopping.entity.TbMember;
import com.shopping.entity.TbOrder;
import com.shopping.entity.TbOrderDetail;

/**
 * Description: <br/>
 * Date: 2018年5月6日 下午1:16:00 <br/>
 * 
 * @author liutao
 * @version
 * @see
 */
public class BizDaoImpl extends BaseDao implements BizDao {

    @Override
    // 查询产品类别
    public List<TbBigType> getBigName() {
        List<TbBigType> list = new ArrayList<TbBigType>();
        try {
            String sql = "SELECT * FROM tb_bigtype";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbBigType tbt = new TbBigType();
                tbt.setId(rs.getInt(1));
                tbt.setBigName(rs.getString(2));
                tbt.setCreateTime(rs.getDate(3));
                list.add(tbt);

            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);

        }
        // Auto-generated method stub
        return list;
    }

    @Override
    // 查询会员信息
    public List<TbMember> getAllMember() {
        List<TbMember> listMember = new ArrayList<TbMember>();
        try {
            String sql = "SELECT * FROM tb_member";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbMember mem = new TbMember();
                mem.setId(rs.getInt(1));
                mem.setName(rs.getString(2));
                mem.setPassword(rs.getString(3));
                mem.setReallyName(rs.getString(4));
                mem.setAge(rs.getInt(5));
                mem.setProfession(rs.getString(6));
                mem.setEmail(rs.getString(7));
                mem.setQuestion(rs.getString(8));
                mem.setResult(rs.getString(9));
                listMember.add(mem);

            }

        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return listMember;
    }

    // 按条件判断会员是否存在
    @Override
    public boolean tbmember(String name, String password) {
        boolean flag = false;
        try {
            String sql = "SELECT * FROM tb_member WHERE NAME=? AND PASSWORD=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, name);
            pst.setString(2, password);
            rs = pst.executeQuery();
            while (rs.next()) {
                flag = true;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return flag;
    }

    // 查询销售排行榜
    @Override
    public List<TbGoods> getGoods() {
        List<TbGoods> listgoods = new ArrayList<TbGoods>();
        try {
            String sql = "SELECT temp.*,@i := @i + 1 AS num FROM(SELECT d.* FROM ( SELECT * FROM tb_goods ) d,(SELECT @i:=0) t ORDER BY d.number DESC) AS temp;";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbGoods tg = new TbGoods();
                tg.setId(rs.getInt(1));
                tg.setBigId(rs.getInt(2));
                tg.setSmallId(rs.getInt(3));
                tg.setGoodsName(rs.getString(4));
                tg.setGoodsFrom(rs.getString(5));
                tg.setIntroduce(rs.getString(6));
                tg.setCreateTime(rs.getDate(7));
                tg.setNowPrice(rs.getDouble(8));
                tg.setFreePrice(rs.getDouble(9));
                tg.setNumber(rs.getInt(10));
                tg.setPirture(rs.getString(11));
                tg.setMark(rs.getInt(12));
                tg.setNum(rs.getInt(13));
                listgoods.add(tg);

            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }

        // Auto-generated method stub
        return listgoods;
    }

    @Override
    // 查询产品，有库存非特价
    public List<TbGoods> getAllGoods() {
        List<TbGoods> listgoods = new ArrayList<TbGoods>();
        try {
            String sql = " SELECT * FROM tb_goods WHERE  mark=0 ORDER BY number DESC";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbGoods tg = new TbGoods();
                tg.setId(rs.getInt(1));
                tg.setBigId(rs.getInt(2));
                tg.setSmallId(rs.getInt(3));
                tg.setGoodsName(rs.getString(4));
                tg.setGoodsFrom(rs.getString(5));
                tg.setIntroduce(rs.getString(6));
                tg.setCreateTime(rs.getDate(7));
                tg.setNowPrice(rs.getDouble(8));
                tg.setFreePrice(rs.getDouble(9));
                tg.setNumber(rs.getInt(10));
                tg.setPirture(rs.getString(11));
                tg.setMark(rs.getInt(12));
                listgoods.add(tg);

            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }

        // Auto-generated method stub
        return listgoods;
    }

    @Override
    // 查询特价产品mark=1
    public List<TbGoods> getFreeGoods() {
        List<TbGoods> freegoods = new ArrayList<TbGoods>();
        try {
            String sql = "SELECT * FROM tb_goods WHERE mark=1";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbGoods fg = new TbGoods();
                fg.setId(rs.getInt(1));
                fg.setBigId(rs.getInt(2));
                fg.setSmallId(rs.getInt(3));
                fg.setGoodsName(rs.getString(4));
                fg.setGoodsFrom(rs.getString(5));
                fg.setIntroduce(rs.getString(6));
                fg.setCreateTime(rs.getDate(7));
                fg.setNowPrice(rs.getDouble(8));
                fg.setFreePrice(rs.getDouble(9));
                fg.setNumber(rs.getInt(10));
                fg.setPirture(rs.getString(11));
                fg.setMark(rs.getInt(12));
                freegoods.add(fg);

            }

        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);

        }
        // Auto-generated method stub
        return freegoods;
    }

    @Override
    // 获取公告标题
    public List<TbAffiche> getAllTitle() {
        List<TbAffiche> listAffiche = new ArrayList<TbAffiche>();
        try {
            String sql = "select * from tb_affiche";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbAffiche ta = new TbAffiche();
                ta.setId(rs.getInt(1));
                ta.setName(rs.getString(2));
                ta.setContent(rs.getString(3));
                ta.setIssueTime(rs.getDate(4));
                listAffiche.add(ta);

            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);
        }

        // Auto-generated method stub
        return listAffiche;
    }

    @Override
    public List<TbLink> getAllTink() {
        List<TbLink> listlink = new ArrayList<TbLink>();
        try {
            String sql = "SELECT * FROM tb_link";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbLink tl = new TbLink();
                tl.setId(rs.getInt(1));
                tl.setLinkName(rs.getString(2));
                tl.setLinkAddress(rs.getString(3));
                listlink.add(tl);

            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return listlink;
    }

    @Override
    // 通过型号id查询
    public List<TbGoods> getGoodsType(int id) {
        List<TbGoods> listgoods = new ArrayList<TbGoods>();
        try {
            String sql = "SELECT * FROM tb_goods WHERE bigId=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbGoods fg = new TbGoods();
                fg.setId(rs.getInt(1));
                fg.setBigId(rs.getInt(2));
                fg.setSmallId(rs.getInt(3));
                fg.setGoodsName(rs.getString(4));
                fg.setGoodsFrom(rs.getString(5));
                fg.setIntroduce(rs.getString(6));
                fg.setCreateTime(rs.getDate(7));
                fg.setNowPrice(rs.getDouble(8));
                fg.setFreePrice(rs.getDouble(9));
                fg.setNumber(rs.getInt(10));
                fg.setPirture(rs.getString(11));
                fg.setMark(rs.getInt(12));
                listgoods.add(fg);

            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();

        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return listgoods;
    }

    @Override
    public List<TbAffiche> getAllTitle(int id) {
        List<TbAffiche> listaffiche = new ArrayList<TbAffiche>();
        try {
            String sql = "SELECT * FROM tb_affiche WHERE id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbAffiche ta = new TbAffiche();
                ta.setId(rs.getInt(1));
                ta.setName(rs.getString(2));
                ta.setContent(rs.getString(3));
                ta.setIssueTime(rs.getDate(4));
                listaffiche.add(ta);

            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return listaffiche;
    }

    @Override
    public boolean uptbmember(TbMember tbmember) {

        return false;
    }

    @Override
    // 依据id查询所有产品
    public List<TbGoods> getGoods(int id) {
        List<TbGoods> listgoods = new ArrayList<TbGoods>();
        try {
            String sql = "SELECT * FROM tb_goods WHERE id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbGoods fg = new TbGoods();
                fg.setId(rs.getInt(1));
                fg.setBigId(rs.getInt(2));
                fg.setSmallId(rs.getInt(3));
                fg.setGoodsName(rs.getString(4));
                fg.setGoodsFrom(rs.getString(5));
                fg.setIntroduce(rs.getString(6));
                fg.setCreateTime(rs.getDate(7));
                fg.setNowPrice(rs.getDouble(8));
                fg.setFreePrice(rs.getDouble(9));
                fg.setNumber(rs.getInt(10));
                fg.setPirture(rs.getString(11));
                fg.setMark(rs.getInt(12));
                listgoods.add(fg);
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);

        }
        // Auto-generated method stub
        return listgoods;
    }

    @Override
    // 依据产品排行查询产品条数
    public int getResultTen() {
        int flag = 0;
        try {
            String sql = "SELECT COUNT(1) FROM (SELECT temp.*,@i := @i + 1 AS num FROM(SELECT d.* FROM ( SELECT * FROM tb_goods ) d,(SELECT @i:=0) t ORDER BY d.number DESC) AS temp) AS tp";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                flag = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    // 添加会员信息
    @Override
    public boolean addtbmember(TbMember tbmember) {
        boolean flag = false;
        try {
            String sql = "INSERT INTO tb_member(NAME,PASSWORD,reallyName,age,profession,email,question,result) VALUES(?,?,?,?,?,?,?,?)";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, tbmember.getName());
            pst.setString(2, tbmember.getPassword());
            pst.setString(3, tbmember.getReallyName());
            pst.setInt(4, tbmember.getAge());
            pst.setString(5, tbmember.getProfession());
            pst.setString(6, tbmember.getEmail());
            pst.setString(7, tbmember.getQuestion());
            pst.setString(8, tbmember.getResult());
            flag = pst.execute();

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }

        return flag;
    }

    // 按条件判断查询会员信息
    @Override
    public boolean getonetbmember(TbMember tbMember) {
        boolean flag = false;
        try {
            String sql = "SELECT * FROM `tb_member` WHERE NAME=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, tbMember.getName());
            rs = pst.executeQuery();
            while (rs.next()) {
                flag = true;
            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);
        }

        return flag;
    }

    // 按条件查询会员信息
    @Override
    public TbMember getonttbmember(TbMember tbMember) {
        TbMember mem = new TbMember();
        try {
            String sql = "SELECT * FROM `tb_member` WHERE NAME=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, tbMember.getName());
            rs = pst.executeQuery();
            while (rs.next()) {

                mem.setId(rs.getInt(1));
                mem.setName(rs.getString(2));
                mem.setPassword(rs.getString(3));
                mem.setReallyName(rs.getString(4));
                mem.setAge(rs.getInt(5));
                mem.setProfession(rs.getString(6));
                mem.setEmail(rs.getString(7));
                mem.setQuestion(rs.getString(8));
                mem.setResult(rs.getString(9));

            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return mem;
    }

    // 修改用户登录密码
    @Override
    public int updatetbmember(TbMember tbmember) {
        int flag = 0;
        try {
            String sql = "UPDATE `tb_member` SET PASSWORD=? WHERE NAME=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, tbmember.getPassword());
            pst.setString(2, tbmember.getName());
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    // 按条件查询管理员信息
    @Override
    public TbManager getoneManager(TbManager tbManager) {

        try {
            String sql = "SELECT * FROM `tb_manager` WHERE account=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, tbManager.getAccount());
            rs = pst.executeQuery();
            while (rs.next()) {

                tbManager.setId(rs.getInt(1));
                tbManager.setAccount(rs.getString(2));
                tbManager.setPassword(rs.getString(3));
                tbManager.setName(rs.getString(4));
                tbManager.setSign(rs.getInt(5));

            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return tbManager;
    }

    // 按条件判断管理员信息
    @Override
    public boolean tb_manager(String name, String password) {

        boolean flag = false;
        try {
            String sql = "SELECT * FROM `tb_manager` WHERE account=? AND PASSWORD=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, name);
            pst.setString(2, password);
            rs = pst.executeQuery();
            while (rs.next()) {
                flag = true;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return flag;
    }

    @Override
    public List<Manager> getAllManager() {
        List<Manager> listman = new ArrayList<Manager>();
        try {
            String sql = "SELECT * FROM tb_manager";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                Manager man = new Manager();
                man.setId(rs.getInt(1));
                man.setAccount(rs.getString(2));
                man.setPassword(rs.getString(3));
                man.setName(rs.getString(4));
                man.setSign(rs.getInt(5));
                listman.add(man);
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return listman;
    }

    @Override
    public List<Member> getAllMembe() {
        List<Member> listmen = new ArrayList<Member>();
        try {
            String sql = "SELECT * FROM tb_member";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                Member mem = new Member();
                mem.setId(rs.getInt(1));
                mem.setName(rs.getString(2));
                mem.setPassword(rs.getString(3));
                mem.setReallyName(rs.getString(4));
                mem.setAge(rs.getInt(5));
                mem.setProfession(rs.getString(6));
                mem.setEmail(rs.getString(7));
                mem.setQuestion(rs.getString(8));
                mem.setResult(rs.getString(9));
                listmen.add(mem);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return listmen;
    }

    @Override
    public List<Member> getMenberByid(int id) {
        List<Member> listm = new ArrayList<Member>();
        try {
            String sql = "SELECT * FROM tb_member WHERE id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                Member mem = new Member();
                mem.setId(rs.getInt(1));
                mem.setName(rs.getString(2));
                mem.setPassword(rs.getString(3));
                mem.setReallyName(rs.getString(4));
                mem.setAge(rs.getInt(5));
                mem.setProfession(rs.getString(6));
                mem.setEmail(rs.getString(7));
                mem.setQuestion(rs.getString(8));
                mem.setResult(rs.getString(9));
                listm.add(mem);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return listm;
    }

    @Override
    public int getMenberById(int id) {
        int flag = 0;
        try {
            String sql = "delete from tb_member where id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    public int getMenageById(int id) {
        int flag = 0;
        try {
            String sql = "delete from tb_manager where id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    public int addManager(Manager man) {
        int flag = 0;
        try {
            String sql = "INSERT INTO `tb_manager` (`account`,`password`,NAME,`sign`) VALUE (?,?,?,?)";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, man.getAccount());
            pst.setString(2, man.getPassword());
            pst.setString(3, man.getName());
            pst.setInt(4, man.getSign());
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return flag;
    }

    @Override
    public int update(Manager man) {
        int flag = 0;
        try {
            String sql = "UPDATE `tb_manager` SET `password`=? WHERE account=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, man.getPassword());
            pst.setString(2, man.getAccount());
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    public int getCount(int id) {
        int flag = 0;
        try {
            String sql = "SELECT COUNT(1) FROM tb_goods WHERE bigId=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                flag = rs.getInt(1);
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    public int getFreeCount(int mark) {
        int flag = 0;
        try {
            String sql = "SELECT COUNT(1) FROM tb_goods WHERE mark=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, mark);
            rs = pst.executeQuery();
            while (rs.next()) {
                flag = rs.getInt(1);
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    // 按姓名修改tbmember会员信息
    @Override
    public int updatetbmemberone(TbMember tbMember) {
        int flag = 0;
        try {
            String sql = "UPDATE `tb_member` SET PASSWORD=?,reallyName=?, age=?,profession=?,email=? WHERE NAME=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, tbMember.getPassword());
            pst.setString(2, tbMember.getReallyName());
            pst.setInt(3, tbMember.getAge());
            pst.setString(4, tbMember.getProfession());
            pst.setString(5, tbMember.getEmail());
            pst.setString(6, tbMember.getName());
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            close(con, pst, rs);
        }

        return flag;
    }

    @Override
    // 通过id获取TbGoods对象
    public TbGoods getShowGoods(int id) {
        TbGoods tg = null;
        try {
            String sql = "SELECT  * FROM  tb_goods WHERE id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                tg = new TbGoods();
                tg.setId(rs.getInt(1));
                tg.setBigId(rs.getInt(2));
                tg.setSmallId(rs.getInt(3));
                tg.setGoodsName(rs.getString(4));
                tg.setGoodsFrom(rs.getString(5));
                tg.setIntroduce(rs.getString(6));
                tg.setCreateTime(rs.getDate(7));
                tg.setNowPrice(rs.getDouble(8));
                tg.setFreePrice(rs.getDouble(9));
                tg.setNumber(rs.getInt(10));
                tg.setPirture(rs.getString(11));
                tg.setMark(rs.getInt(12));
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return tg;
    }

    @Override
    // 通过用户名获取真实姓名
    public String getReallyName(String name) {
        String reallyname = "";
        try {
            String sql = "SELECT reallyName FROM tb_member WHERE NAME=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, name);
            rs = pst.executeQuery();
            while (rs.next()) {
                reallyname = rs.getString(1);

            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return reallyname;
    }

    @Override
    // 插入数据到订单表
    public boolean insertOrder(TbOrder order) {
        boolean flag = false;
        try {
            String sql = "insert into tb_order values (null,?,?,?,?,?,?,?,?,?,now())";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, order.getNumber());
            pst.setString(2, order.getName());
            pst.setString(3, order.getReallyName());
            pst.setString(4, order.getAddress());
            pst.setString(5, order.getTel());
            pst.setString(6, order.getSetMoney());
            pst.setString(7, order.getPost());
            pst.setString(8, order.getBz());
            pst.setString(9, order.getSign());
            flag = pst.execute();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    public boolean insertOrderDetail(TbOrderDetail orderdetail) {
        boolean flag = false;
        try {
            String sql = "insert into tb_orderDetail values (null,?,?,?,?)";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, orderdetail.getOrderNumber());
            pst.setInt(2, orderdetail.getGoodsId());
            pst.setDouble(3, orderdetail.getPrice());
            pst.setInt(4, orderdetail.getNumber());
            flag = pst.execute();

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    // 更新产品数量
    public int updateGoodsNumber(int number, Integer id) {
        int flag = 0;
        try {
            String sql = "update tb_goods set number=number+? where id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, number);
            pst.setInt(2, id.intValue());
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);

        }

        // Auto-generated method stub
        return flag;
    }

    @Override
    // 通过会员名查看订单
    public List<TbOrder> getAllOrder(String name) {
        List<TbOrder> list = new ArrayList<TbOrder>();
        try {
            String sql = "SELECT * FROM tb_order WHERE NAME=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, name);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbOrder order = new TbOrder();
                order.setId(rs.getInt(1));
                order.setNumber(rs.getString(2));
                order.setName(rs.getString(3));
                order.setReallyName(rs.getString(4));
                order.setAddress(rs.getString(5));
                order.setTel(rs.getString(6));
                order.setSetMoney(rs.getString(7));
                order.setPost(rs.getString(8));
                order.setBz(rs.getString(9));
                order.setSign(rs.getString(10));
                order.setCreateTime(rs.getString(11));
                list.add(order);
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return list;
    }

    @Override
    // 通过订单编码查看所有订单
    public List<GoodsDetail> getAllDetail(String number) {
        List<GoodsDetail> list = new ArrayList<GoodsDetail>();
        // Auto-generated method stub
        try {
            // String sql = "SELECT * FROM tb_orderdetail WHERE orderNumber=?";
            String sql = "SELECT b.`orderNumber`,a.`goodsName`,b.`number`,b.`price` FROM tb_orderdetail b,tb_goods a WHERE b.goodsID=a.Id AND b.orderNumber=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, number);
            rs = pst.executeQuery();
            while (rs.next()) {
                GoodsDetail detail = new GoodsDetail();
                detail.setOrderNumber(rs.getString(1));
                detail.setGoodsName(rs.getString(2));
                detail.setNumber(rs.getInt(3));
                detail.setPrice(rs.getFloat(4));
                list.add(detail);

            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return list;
    }

    @Override
    // 通过id号查询获取名称
    public String getGoodsName(int id) {
        String name = "";
        try {
            String sql = "SELECT goodsName FROM tb_goods WHERE id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                name = rs.getString(1);
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);

        }
        // Auto-generated method stub
        return name;
    }

    @Override
    // 查询所有订单
    public List<TbOrder> getAllOrder() {
        List<TbOrder> listorder = new ArrayList<TbOrder>();
        try {
            String sql = "SELECT * FROM `tb_order`";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbOrder order = new TbOrder();
                order.setId(rs.getInt(1));
                order.setNumber(rs.getString(2));
                order.setName(rs.getString(3));
                order.setReallyName(rs.getString(4));
                order.setAddress(rs.getString(5));
                order.setTel(rs.getString(6));
                order.setSetMoney(rs.getString(7));
                order.setPost(rs.getString(8));
                order.setBz(rs.getString(9));
                order.setSign(rs.getString(10));
                order.setCreateTime(rs.getString(11));
                listorder.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return listorder;
    }

    @Override
    public int updateOrder(String number) {
        int flag = 0;
        try {
            String sql = "UPDATE `tb_order` SET  `sign`=1 WHERE number=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, number);
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    public List<TbOrder> getAllOrderbySign(int sign) {
        List<TbOrder> listorder = new ArrayList<TbOrder>();
        try {
            String sql = "SELECT * FROM `tb_order` where sign = ?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, sign);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbOrder order = new TbOrder();
                order.setId(rs.getInt(1));
                order.setNumber(rs.getString(2));
                order.setName(rs.getString(3));
                order.setReallyName(rs.getString(4));
                order.setAddress(rs.getString(5));
                order.setTel(rs.getString(6));
                order.setSetMoney(rs.getString(7));
                order.setPost(rs.getString(8));
                order.setBz(rs.getString(9));
                order.setSign(rs.getString(10));
                order.setCreateTime(rs.getString(11));
                listorder.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return listorder;
    }

    @Override
    public int deleteOrder(String number) {
        int flag = 0;
        try {
            String sql = "DELETE FROM `tb_order` WHERE number=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, number);
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return flag;
    }

    @Override
    public TbOrder getOrderDetailByNumber(String number) {
        TbOrder order = new TbOrder();
        try {
            String sql = "SELECT * FROM `tb_order` WHERE number = ?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, number);
            rs = pst.executeQuery();
            while (rs.next()) {
                order.setId(rs.getInt(1));
                order.setNumber(rs.getString(2));
                order.setName(rs.getString(3));
                order.setReallyName(rs.getString(4));
                order.setAddress(rs.getString(5));
                order.setTel(rs.getString(6));
                order.setSetMoney(rs.getString(7));
                order.setPost(rs.getString(8));
                order.setBz(rs.getString(9));
                order.setSign(rs.getString(10));
                order.setCreateTime(rs.getString(11));
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return order;
    }

    @Override
    // 产品详情
    public List<GoodsDetail> getGoodsDetailByNumber(String number) {
        List<GoodsDetail> listgoods = new ArrayList<GoodsDetail>();
        try {
            String sql = "SELECT g.goodsName,a.number,a.price FROM `tb_goods` g JOIN `tb_orderdetail` a ON g.`id` = a.`goodsId` WHERE a.`orderNumber`=?";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, number);
            rs = pst.executeQuery();
            while (rs.next()) {
                GoodsDetail goodsdetail = new GoodsDetail();
                goodsdetail.setGoodsName(rs.getString(1));
                goodsdetail.setNumber(rs.getInt(2));
                goodsdetail.setPrice(rs.getFloat(3));
                listgoods.add(goodsdetail);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return listgoods;
    }

    // 以小类别外关键为条件查询信息
    @Override
    public List<SmallTypeForm> selectOneBigId(Integer bigId) {
        List<SmallTypeForm> list = new ArrayList<SmallTypeForm>();
        SmallTypeForm small = null;
        try {
            this.pst = con.prepareStatement("select * from tb_smallType where bigId=?");
            pst.setString(1, bigId.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                small = new SmallTypeForm();
                small.setId(Integer.valueOf(rs.getString(1)));
                small.setBigId(Integer.valueOf(rs.getString(2)));
                small.setSmallName(rs.getString(3));
                small.setCreaTime(rs.getString(4));
                list.add(small);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    // 以数据库流水号为条件查询小类别的名称
    @Override
    public String selectName(Integer id) {
        String name = null;
        try {
            this.pst = con.prepareStatement("select * from tb_smallType where id=?");
            pst.setString(1, id.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                name = rs.getString("smallName");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return name;
    }

    // 删除操作
    @Override
    public boolean deleteSmall(Integer id) {
        try {
            pst = con.prepareStatement("delete from tb_smallType where id=?");
            pst.setString(1, id.toString());
            pst.executeUpdate();
            pst.close();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    // 修改操作
    public void updateSmall(SmallTypeForm form) {
        try {
            pst = con.prepareStatement("update tb_smallType set bigId=?,smallName=? where id=?");
            pst.setString(1, form.getBigId().toString());
            pst.setString(2, form.getSmallName());
            pst.setString(3, form.getId().toString());
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    // 添加操作
    public void insertSmall(SmallTypeForm form) {
        try {
            pst = con.prepareStatement("insert into tb_smallType values (null,?,?,now())");
            pst.setString(1, form.getBigId().toString());
            pst.setString(2, form.getSmallName());
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    // 以数据库流水号为条件查询信息
    @Override
    public SmallTypeForm selectOneBig(Integer id) {
        SmallTypeForm small = null;
        try {
            this.pst = con.prepareStatement("select * from tb_smallType where id=?");
            pst.setString(1, id.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                small = new SmallTypeForm();
                small.setId(Integer.valueOf(rs.getString(1)));
                small.setBigId(Integer.valueOf(rs.getString(2)));
                small.setSmallName(rs.getString(3));
                small.setCreaTime(rs.getString(4));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return small;
    }

    // 全部查询的操作
    @Override
    public List<SmallTypeForm> selectSmall() {
        List<SmallTypeForm> list = new ArrayList<SmallTypeForm>();
        SmallTypeForm small = null;
        try {
            this.pst = con.prepareStatement("select * from tb_smallType order by id DESC");
            rs = pst.executeQuery();
            while (rs.next()) {
                small = new SmallTypeForm();
                small.setId(Integer.valueOf(rs.getString(1)));
                small.setBigId(Integer.valueOf(rs.getString(2)));
                small.setSmallName(rs.getString(3));
                small.setCreaTime(rs.getString(4));
                list.add(small);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    @Override
    public boolean deleteBig(Integer id) {
        try {
            pst = con.prepareStatement("delete from tb_bigType where id=?");
            pst.setString(1, id.toString());
            pst.executeUpdate();
            pst.close();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    // 添加操作
    public void insertBig(String name) {
        try {
            pst = con.prepareStatement("insert into tb_bigType values (null,?,now())");
            pst.setString(1, name);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    // 全部查询的操作
    @Override
    public List<BigTypeForm> selectBig() {
        List<BigTypeForm> list = new ArrayList<BigTypeForm>();
        BigTypeForm big = null;
        try {
            this.pst = con.prepareStatement("select * from tb_bigType order by id DESC");
            rs = pst.executeQuery();
            while (rs.next()) {
                big = new BigTypeForm();
                big.setId(Integer.valueOf(rs.getString(1)));
                big.setBigName(rs.getString(2));
                big.setCreaTime(rs.getString(3));
                list.add(big);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    // 以商品的大类别的编号为条件查询信息
    @Override
    public List<GoodsForm> selectBig(Integer big) {
        List<GoodsForm> list = new ArrayList<GoodsForm>();
        GoodsForm goods = null;
        try {
            pst = con.prepareStatement("select * from tb_goods where bigId=? order by id DESC");
            pst.setInt(1, big.intValue());
            rs = pst.executeQuery();
            while (rs.next()) {
                goods = new GoodsForm();
                goods.setId(Integer.valueOf(rs.getString(1)));
                goods.setBig(Integer.valueOf(rs.getString(2)));
                goods.setSmall(Integer.valueOf(rs.getString(3)));
                goods.setName(rs.getString(4));
                goods.setFrom(rs.getString(5));
                goods.setIntroduce(rs.getString(6));
                goods.setCreaTime(rs.getString(7));
                goods.setNowPrice(Float.valueOf(rs.getString(8)));
                goods.setFreePrice(Float.valueOf(rs.getString(9)));
                goods.setNumber(Integer.valueOf(rs.getString(10)));
                goods.setPriture(rs.getString(11));
                goods.setMark(Integer.valueOf(rs.getString(12)));
                list.add(goods);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    @Override
    public TbAffiche getAllTitle1(int id) {
        TbAffiche tba = new TbAffiche();
        try {
            String sql = "SELECT * FROM tb_affiche WHERE id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                tba.setId(rs.getInt(1));
                tba.setName(rs.getString(2));
                tba.setContent(rs.getString(3));
                tba.setIssueTime(rs.getDate(4));
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return tba;
    }

    @Override
    public List<TbAffiche> getAllAffiche() {
        List<TbAffiche> listaffiche = new ArrayList<TbAffiche>();
        try {
            String sql = "SELECT * FROM tb_affiche ORDER BY id DESC";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbAffiche ta = new TbAffiche();
                ta.setId(rs.getInt(1));
                ta.setName(rs.getString(2));
                ta.setContent(rs.getString(3));
                ta.setIssueTime(rs.getDate(4));
                listaffiche.add(ta);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return listaffiche;
    }

    @Override
    public int updateAffiche(TbAffiche affiche) {
        int flag = 0;
        try {
            String sql = "UPDATE `tb_affiche` SET `name`=?,content=?,issueTime =NOW()  WHERE id = ?";
            pst = getCon().prepareCall(sql);
            pst.setString(1, affiche.getName());
            pst.setString(2, affiche.getContent());
            /* pst.setDate(3, (Date) affiche.getIssueTime()); */
            pst.setInt(3, affiche.getId());
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return flag;
    }

    @Override
    public int insertAffiche(String name, String content) {
        int flag = 0;
        try {
            String sql = "INSERT INTO `tb_affiche` (`name`,content,issueTime) VALUE(?,?,NOW())";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, name);
            pst.setString(2, content);
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return flag;
    }

    @Override
    public int deleteAffiche(int id) {
        int flag = 0;
        try {
            String sql = "DELETE FROM `tb_affiche` WHERE id = ?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            flag = pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return flag;
    }

    @Override
    public List<TbLink> getAllLink() {
        List<TbLink> list = new ArrayList<TbLink>();
        try {
            String sql = "SELECT * FROM tb_link ORDER BY id DESC";
            pst = getCon().prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                TbLink link = new TbLink();
                link.setId(rs.getInt(1));
                link.setLinkName(rs.getString(2));
                link.setLinkAddress(rs.getString(3));
                list.add(link);

            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }

        // Auto-generated method stub
        return list;
    }

    @Override
    public int linkInsert(TbLink link) {
        int flag = 0;
        try {
            String sql = "insert into tb_link(linkName,linkAddress) values(?,?)";
            pst = getCon().prepareStatement(sql);
            pst.setString(1, link.getLinkName());
            pst.setString(2, link.getLinkAddress());
            flag = pst.executeUpdate();

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }

    @Override
    public int linkDelete(int id) {
        int flag = 0;
        try {
            String sql = "DELETE FROM tb_link WHERE id=?";
            pst = getCon().prepareStatement(sql);
            pst.setInt(1, id);
            flag = pst.executeUpdate();

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        // Auto-generated method stub
        return flag;
    }
}
